package com.huidao.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;



import com.huidao.entity.GoodsType;
import com.huidao.util.JDBCUtil;

public class GoodsTypeDaoImp implements GoodsTypeDao {

	Connection conn = null;
	PreparedStatement ps =null;
	ResultSet rs =null;
	@Override
	public List<GoodsType> getGoodTypeList() {
		List<GoodsType> list = new ArrayList<GoodsType>();
		String sql = "SELECT * FROM GOODSTYPE ";
		try {
			conn=JDBCUtil.getConn();
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()){
				GoodsType gt = new GoodsType();
				gt.setGoodstype_id(rs.getLong("GOODSTYPE_ID"));
				gt.setGoodstype_name(rs.getString("goodstype_name"));
				list.add(gt);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.free(conn, ps, rs);
		}
		return list;
	}
	@Override
	public boolean addGoodsType(GoodsType goodsType) {
		String sql = "INSERT INTO GOODSTYPE (GOODSTYPE_NAME,GOODSTYPE_ID) VALUES(?,?)";
		try {
			conn = JDBCUtil.getConn();
			ps = conn.prepareStatement(sql);
			ps.setString(1, goodsType.getGoodstype_name());
			ps.setLong(2, goodsType.getGoodstype_id());
			int i = ps.executeUpdate();
			if (i > 0) {
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.free(conn, ps, rs);
		}
		return false;
	}
	public boolean delGoodsType(long gid) {
		String sql = "DELETE FROM GOODSTYPE WHERE GOODSTYPE_ID=?";
		try {
			conn = JDBCUtil.getConn();
			ps = conn.prepareStatement(sql);
			ps.setLong(1, gid);
			int i = ps.executeUpdate();
			if (i > 0) {
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtil.free(conn, ps, rs);
		}
		return false;
	}
	public int total(String keyName, long gid) {
		String sql = "SELECT COUNT(*) FROM GOODSTYPE WHERE 1=1";
		if (keyName != null && !keyName.trim().equals("")) {
			sql = sql + "  AND GOODSTYPE_NAME LIKE '%"+ keyName +"%'";
		}
		if (gid != 0) {
			sql = sql + "  AND GOODSTYPE_ID = ' " + gid + " ' ";
		}
		try {
			conn = JDBCUtil.getConn();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			if (rs.next()) {
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtil.free(conn, ps, rs);
		}
		return 0;
	}
	@Override
	public boolean updateGoodsType(GoodsType goodsType) {
		String sql = "UPDATE GOODSTYPE SET GOODSTYPE_NAME = ? WHERE GOODSTYPE_ID =?";
		try {
			conn = JDBCUtil.getConn();
			ps = conn.prepareStatement(sql);
			ps.setString(1, goodsType.getGoodstype_name());
			ps.setLong(2, goodsType.getGoodstype_id());
			int i = ps.executeUpdate();
			if (i > 0) {
				return true;
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtil.free(conn, ps, rs);
		}
		return false;
	}
	@Override
	public List<GoodsType> getListGoodsType(String keyName, int currenpage,
			int pagesize, long gid) {
		List<GoodsType> list = new ArrayList<GoodsType>();
		String sql = "SELECT * FROM  GOODSTYPE A  LEFT JOIN USER B ON A.USER_ID = B.USER_ID  WHERE 1=1";
		if (keyName != null && !keyName.trim().equals("")){
			sql = sql + "  AND GOODSTYPE_NAME LIKE '%" + keyName + "%'";
		}
		if (gid != 0) {
			sql = sql + " AND GOODSTYPE_ID = ' " + gid + " ' ";
		}
		sql = sql + " LIMIT ?,?";
		try {
			conn = JDBCUtil.getConn();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, (currenpage - 1) * pagesize);
			ps.setInt(2, pagesize);
			rs = ps.executeQuery();
			while (rs.next()) {
				GoodsType gt = new GoodsType();
				gt.setGoodstype_name(rs.getString("GOODSTYPE_NAME"));
				gt.setGoodstype_id(rs.getLong("GOODSTYPE_ID"));
				list.add(gt);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtil.free(conn, ps, rs);
		}
		return list;
	}
}
